using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections.Generic;
/// <summary>
/// Summary description for FacilityTypeDAO
/// </summary>
public class FacilityTypeDAO:DBAccess
{
	#region sql Commands   
    string SQL_SELECT_ALL = "select ID,[Name],Description,ImageURL,State,ModifiedDate  from FacilityType where [Name] like @name";

    //string SQL_SELECT_ALL = "select ID, Name, Description, State, ModifiedDate, ModifiedBy from FacilityType";

    string SQL_SELECT_BY_ID = "select * from FacilityType where ID=@id";
    string SQL_INSERT = "insert into FacilityType([Name], [Description], ImageURL, Article,ModifiedDate,ModifiedBy,State) values (@Name, @Description, @ImageURL, @Article,@ModifiedDate,@ModifiedBy,@State)";
    string SQL_UPDATE = "update FacilityType set Name = @Name, Description= @Description,ImageURL=@ImageURL ,Article=@Article,ModifiedDate=@ModifiedDate,ModifiedBy=@ModifiedBy,State=@State where ID = @id";
    string SQL_DELETE = "delete from FacilityType where ID = @id";
    #endregion

    #region fields
    string message = "";
    #endregion

    #region constructors
    public FacilityTypeDAO()
    {

    }
    #endregion

    #region properties
    public string Message
    {
        get { return message; }
        set { message = value; }
    }
    #endregion

    #region Methos
    /// <summary>
    /// Get FacilityType by id
    /// </summary>
    /// <param name="id"></param>
    /// <returns></returns>
    public FacilityType GetFacilityType(int id)
    {
        FacilityType facType = new FacilityType();
        try
        {
            
            SqlCommand command = new SqlCommand(SQL_SELECT_BY_ID, conn);
            if (Connect())
            {
                command.Parameters.AddWithValue("@id", id);
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    facType.Id = dr.GetInt32(0);
                    facType.Name = dr.GetString(1);                                   
                    facType.Description = dr.GetString(2);
                    facType.ImageURL = dr.GetString(3);             
                    facType.State = dr.GetBoolean(4);
                    facType.ModifiedDate = dr.GetDateTime(5);                 
                    
                }
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return facType;
    }
    /// <summary>
    /// Get all FacilityTypes 
    /// </summary>
    /// <returns></returns>
    public IList<FacilityType> GetAllFacilityTypes(string name)
    {
        IList<FacilityType> list = new List<FacilityType>();
        try
        {
            SqlCommand command = new SqlCommand(SQL_SELECT_ALL, conn);
            command.Parameters.AddWithValue("@name","%"+name+"%");
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    FacilityType facType = new FacilityType();
                    facType.Id = dr.GetInt32(0);
                    facType.Name = dr.GetString(1);
                    facType.Description = dr.GetString(2);
                    facType.ImageURL = dr.GetString(3);
                    facType.State = dr.GetBoolean(4);                                
                    facType.ModifiedDate = dr.GetDateTime(5);               
                    
                    list.Add(facType);
                }
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return list;
    }
    /// <summary>
    /// Insert new FacilityType into database
    /// </summary>
    /// <param name="tour"></param>
    /// <returns></returns>
    public int Insert(FacilityType facType)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_INSERT, conn);
            command.Parameters.AddWithValue("@Name", facType.Name);                 
            command.Parameters.AddWithValue("@Description", facType.Description);
            command.Parameters.AddWithValue("@ImageURL", facType.ImageURL);           
            command.Parameters.AddWithValue("@Article", facType.Article);          
            command.Parameters.AddWithValue("@ModifiedDate", facType.ModifiedDate);
            command.Parameters.AddWithValue("@ModifiedBy", facType.ModifiedBy);
            command.Parameters.AddWithValue("@State", facType.State);
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
                command.CommandText = "select @@IDENTITY from FacilityType";
                facType.Id = int.Parse(command.ExecuteScalar().ToString());
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRows;
    }

    /// <summary>
    /// Update FacilityType
    /// </summary>
    /// <param name="tour"></param>
    /// <returns></returns>
    public int Update(FacilityType facType)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_UPDATE, conn);
            command.Parameters.AddWithValue("@Name", facType.Name);
            command.Parameters.AddWithValue("@Description", facType.Description);
            command.Parameters.AddWithValue("@ImageURL", facType.ImageURL);
            command.Parameters.AddWithValue("@Article", facType.Article);
            command.Parameters.AddWithValue("@ModifiedDate", facType.ModifiedDate);
            command.Parameters.AddWithValue("@ModifiedBy", facType.ModifiedBy);
            command.Parameters.AddWithValue("@State", facType.State);
            command.Parameters.AddWithValue("@id",facType.Id);  
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRows;
    }

    /// <summary>
    /// Remove FacilityType out of database
    /// </summary>
    /// <param name="tour"></param>
    /// <returns></returns>
    public int Delete(int Id)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_DELETE, conn);
            command.Parameters.AddWithValue("@id", Id);
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRows;
    }
    #endregion
}
